/**
 * @(#)ExcelReader.java, 9月 18, 2023.
 * <p>
 * Copyright 2023 yuanfudao.com. All rights reserved.
 * FENBI.COM PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
 */
package yfd;

import lombok.Data;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author leiwenkang
 */
public class ExcelReader1 {
    //用java读excel


    public static void main(String[] args) throws IOException {
        List<InspireConfig> res = readExcel("src/main/resources/3～6年级单元作文通用提纲.xlsx");

        buildSql(res);
    }

    /**
     * inspire_config的配置
     * @param inspireConfigs
     */
    private static void buildSql(List<InspireConfig> inspireConfigs) {
        String sql = "INSERT INTO unit_composition_inspire_config (grade, semester, unit, supportInspire, title, digest, createdTime) VALUES (%s);";

        for (int i = 0; i < inspireConfigs.size(); i++) {
            StringBuilder sb = new StringBuilder();
            InspireConfig config = inspireConfigs.get(i);
            sb.append(config.getGrade());
            sb.append(", ");
            sb.append(config.getSemester());
            sb.append(", ");
            sb.append(config.getUnit());
            sb.append(", ");
            sb.append(config.getSupportInspire());
            sb.append(", ");
            sb.append("'" + config.getTitle() + "'");
            sb.append(", ");
            sb.append("'" + config.getDigest() + "'");
            sb.append(", ");
            sb.append(System.currentTimeMillis());

            System.out.printf(String.format(sql, sb));
            System.out.println();
        }


    }

    public static List<InspireConfig> readExcel(String excelFilePath) throws IOException {

        FileInputStream fis = new FileInputStream(excelFilePath);

        // create a new workbook from the input stream
        Workbook workbook = new XSSFWorkbook(fis);
        // get the first sheet
        Sheet sheet = workbook.getSheetAt(0);

        int firstRow = sheet.getFirstRowNum();
        int lastRow = sheet.getLastRowNum();

        List<InspireConfig> list = new ArrayList<>();
        for (int i = firstRow + 1; i <= lastRow; i++) {
            Row row = sheet.getRow(i);
            // For each row, iterate through all the columns
            int firstCell = row.getFirstCellNum();
            int lastCell = row.getLastCellNum();

            InspireConfig inspireConfig = new InspireConfig();
            for (int j = firstCell; j <= lastCell; j++) {
                Cell cell = row.getCell(j);
                if (j == 1) {
                    inspireConfig.setGrade((int) (cell.getNumericCellValue()));
                } else if (j == 2) {
                    inspireConfig.setSemester((int) (cell.getNumericCellValue()));
                } else if (j == 3) {
                    inspireConfig.setUnit((int) (cell.getNumericCellValue()));
                } else if (j == 9) {
                    inspireConfig.setSupportInspire((int) (cell.getNumericCellValue()));
                } else if (j == 10) {
                    inspireConfig.setTitle(cell.getStringCellValue());
                } else if (j == 11) {
                    inspireConfig.setDigest(cell.getStringCellValue());
                }
            }

            list.add(inspireConfig);
        }

        workbook.close();
        fis.close();

        return list;
    }
}

@Data
class InspireConfig {
    private int grade;
    private int semester;
    private int unit;
    private int supportInspire;
    private String title;
    private String digest;
}